﻿using Delphi.Mimir.Data.Bulk;
using Delphi.Mimir.Data.Entities.Final;
using Delphi.Mimir.Data.Procedures;
using Delphi.Mimir.Wrapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;

namespace Delphi.Mimir.Migrations.Legacy {
    public class OraConnect : IDisposable {

        OracleConnection _conn;

        public OraConnect() {
            _conn = new OracleConnection();
        }

        public OracleConnection Connect() {
            Parameter credentials = ParameterWrapper.GetParameter("EOD", "Mimir", "Login", "Oracle");
            Parameter dataSource = ParameterWrapper.GetParameter("EOD", "Mimir", "Data Source", "Oracle");
            _conn.ConnectionString = string.Format(@"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
                (HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2}))); User Id={3}; Password={4};", 
                dataSource.OutParm1, dataSource.OutParm3, dataSource.OutParm2, credentials.OutParm1, credentials.OutParm2);
            _conn.Open();

            return _conn;
        }

        public void MigrateSymbols(DateTime migrationDate) {
            // make sure connection is set up...  otherwise build connection
            if (_conn == null) {
                Connect();
            }

            // Set the scene for the Oracle query
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = _conn;
            cmd.CommandText = string.Format(@"select distinct sym_cd, nvl(sym_name, ''), 
                sym_exc_cd from mts_raw.eod_symbols where SYM_PROCESS_DT < to_date('{0}', 'YYYY-MM-DD')", migrationDate.ToString("yyyy-MM-dd"));
            cmd.CommandType = CommandType.Text;

            // Prepare the DataTable for BulkLoad later
            DataTable table = new DataTable();
            table.Columns.Add(new DataColumn("sym_id", typeof(int)));
            table.Columns.Add(new DataColumn("sym_cd", typeof(string)));
            table.Columns.Add(new DataColumn("sym_desc", typeof(string)));
            table.Columns.Add(new DataColumn("sym_exc_cd", typeof(string)));
            table.Columns.Add(new DataColumn("sym_load_ts", typeof(DateTime)));

            // Fill the oracle data into the DataTable
            using (OracleDataReader reader = cmd.ExecuteReader()) {
                while(reader.Read()) {
                    DataRow row = table.NewRow();
                    row["sym_cd"] = reader.GetString(0);
                    // Sym Desc can be null, needs to be handled seperatly
                    if(!reader.IsDBNull(1)) {
                        row["sym_desc"] = reader.GetString(1);
                    } else {
                        row["sym_desc"] = string.Empty;
                    }
                    row["sym_exc_cd"] = reader.GetString(2);
                    row["sym_load_ts"] = migrationDate;
                    table.Rows.Add(row);
                }
            }

            // Store the data in SQL Server and process it with Procedure
            string tableName = "raw.eod_symbols";
            Etl.ClearTable(tableName);
            BulkLoad load = BulkLoad.GetInstance();
            load.update(table, tableName, 1000);
            Etl.EodSymbolsApi();
            Etl.ClearTable(tableName);
        }

        public void MigrateQuotes(DateTime migrationDate) {
            // make sure connection is set up...  otherwise build connection
            if (_conn == null) {
                Connect();
            }

            // Set the scene for the Oracle query
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = _conn;

            // Get earliest date in oracle DB
            cmd.CommandText = @"SELECT MIN(qot_trade_date) FROM mts_raw.eod_quotes";
            cmd.CommandType = CommandType.Text;

            DateTime date = new DateTime();
            using(OracleDataReader reader = cmd.ExecuteReader()) {
                while(reader.Read())
                date = reader.GetDateTime(0);
            }

            for (int year = date.Year; year <= migrationDate.Year; ++year) {



                cmd.CommandText = string.Format(@"SELECT DISTINCT     qot_sym_cd
                                                                    , qot_exc_cd
                                                                    , qot_trade_date
                                                                    , qot_period
                                                                    , qot_open
                                                                    , qot_high
                                                                    , qot_low
                                                                    , qot_close
                                                                    , qot_volume
                                                                    , qot_open_interest 
                                                FROM                mts_raw.eod_quotes
                                                WHERE               qot_trade_date < to_date('{0}', 'YYYY-MM-DD')
                                                                    AND EXTRACT(YEAR FROM qot_trade_date) = {1}", migrationDate.ToString("yyyy-MM-dd"), year);
                cmd.CommandType = CommandType.Text;

                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("qot_id", typeof(Int64)));
                table.Columns.Add(new DataColumn("qot_sym_cd", typeof(string)));
                table.Columns.Add(new DataColumn("qot_exc_cd", typeof(string)));
                table.Columns.Add(new DataColumn("qot_trade_dt", typeof(DateTime)));
                table.Columns.Add(new DataColumn("qot_period", typeof(string)));
                table.Columns.Add(new DataColumn("qot_open", typeof(decimal)));
                table.Columns.Add(new DataColumn("qot_high", typeof(decimal)));
                table.Columns.Add(new DataColumn("qot_low", typeof(decimal)));
                table.Columns.Add(new DataColumn("qot_close", typeof(decimal)));
                table.Columns.Add(new DataColumn("qot_volume", typeof(Int64)));
                table.Columns.Add(new DataColumn("qot_open_inerest", typeof(int)));

                using (OracleDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        DataRow row = table.NewRow();
                        row["qot_sym_cd"] = reader.GetString(0);
                        row["qot_exc_cd"] = reader.GetString(1);
                        row["qot_trade_dt"] = reader.GetDateTime(2);
                        row["qot_period"] = reader.GetString(3).ToUpper();
                        row["qot_open"] = reader.GetDecimal(4);
                        row["qot_high"] = reader.GetDecimal(5);
                        row["qot_low"] = reader.GetDecimal(6);
                        row["qot_close"] = reader.GetDecimal(7);
                        row["qot_volume"] = reader.GetInt64(8);
                        row["qot_open_inerest"] = reader.GetInt32(9);
                        table.Rows.Add(row);
                    }
                }
                // Store the data in SQL Server and process it with Procedure
                string tableName = "raw.eod_quotes";
                Etl.ClearTable(tableName);
                BulkLoad load = BulkLoad.GetInstance();
                load.update(table, tableName, 1000);
                Etl.EodSymbolsApi();
                Etl.ClearTable(tableName);
            }
        }

        public void Dispose() {
            _conn.Close();
            _conn.Dispose();
            _conn = null;
        }
    }
}
